if exists (select * from sysobjects where (sysobjects.uid = 9 or sysobjects.uid = 11) and "Name" = 'TRF_ACCOUNT')
begin
 	if exists (select * from TRF_ACCOUNT)
 	begin
 		if exists (select * from sysobjects where (sysobjects.uid = 9 or sysobjects.uid = 11) and (sysobjects.uid = 9 or sysobjects.uid = 11) and "Name" = 'TRF_ACCOUNT_OLD')
 		begin
 			drop table TRF_ACCOUNT_OLD;
 			drop table TRF_CLIENT_OLD;
 		end
 		select * into TRF_ACCOUNT_OLD from TRF_ACCOUNT;
 		select * into TRF_CLIENT_OLD from TRF_CLIENT;
 	end
 	if exists (select * from sysobjects where (sysobjects.uid = 9 or sysobjects.uid = 11) and "Name" = 'TRF_TARIFF')
 	begin	
		if exists (select * from TRF_TARIFF)
 		begin
 			if exists (select * from sysobjects where (sysobjects.uid = 9 or sysobjects.uid = 11) and "Name" = 'TRF_TARIFF_OLD')
 				drop table TRF_TARIFF_OLD;
 			select * into TRF_TARIFF_OLD from TRF_TARIFF;
 		end
		drop table TRF_ACCOUNT;
 		drop table TRF_CLIENT;
		drop table TRF_TARIFF;
	end
	else 
		begin
			drop table TRF_ACCOUNT;
 			drop table TRF_CLIENT;
		end
end
 
if exists (select * from sysobjects where (sysobjects.uid = 9 or sysobjects.uid = 11) and "Name" = 'TRF_TARIFF')
 	if exists (select * from TRF_TARIFF)
 	begin
 		if exists (select * from sysobjects where (sysobjects.uid = 9 or sysobjects.uid = 11) and "Name" = 'TRF_TARIFF_OLD')
 		begin
 			drop table TRF_TARIFF_OLD;
 		end
 		select * into TRF_TARIFF_OLD from TRF_TARIFF;
 	end
else
	drop table TRF_TARIFF;

 create table TRF_TARIFF
 (
 TARIFF_ID integer primary key,
 TARIFF_NAME nvarchar(32) UNIQUE,
 OUT_NET numeric, --price of outgoing call to subscriber of other network
 IN_NET numeric, --price of outgoing call in client network
 IN_ROAMING numeric, --price markup in roaming for incoming call
 OUT_ROAMING numeric --price markup in roaming for outgoing call
 );
 
 create table TRF_CLIENT
 (
 CLIENT_ID integer primary key,
 CLIENT_NAME nvarchar(64),
 PASSPORT_NUMBER integer
 );
 
 create table TRF_ACCOUNT
 (
 ACCOUNT_ID integer primary key,
 CLIENT_ID integer, foreign key (CLIENT_ID) references TRF_CLIENT(CLIENT_ID),
 TARIFF_ID integer, foreign key (TARIFF_ID) references TRF_TARIFF(TARIFF_ID), 
 BALANCE numeric 
 );

if exists (select * from sysobjects where (sysobjects.uid = 9 or sysobjects.uid = 11) and "Name" = 'TRF_INSERT_ACCOUNT')
begin
	drop procedure TRF_INSERT_ACCOUNT;
end

GO

--insert account according to tables logic
CREATE PROCEDURE TRF_INSERT_ACCOUNT(
  @ClientName nvarchar(64),
  @ClientPassport integer,
  @AccountId integer,
  @StartTariff integer,
  @StartBalance numeric	
)
AS
BEGIN
	DECLARE @count integer;
	IF EXISTS (SELECT * FROM TRF_CLIENT WHERE CLIENT_NAME=@ClientName and PASSPORT_NUMBER = @ClientPassport)
	BEGIN
		SELECT @count = (SELECT CLIENT_ID FROM TRF_CLIENT WHERE CLIENT_NAME=@ClientName and PASSPORT_NUMBER = @ClientPassport);
		INSERT INTO TRF_ACCOUNT VALUES(@AccountId,@count,@StartTariff,@StartBalance);
	END
	ELSE
		BEGIN
			SELECT @count = (SELECT MAX(CLIENT_ID) FROM TRF_CLIENT);
			IF (@count IS NULL)
			BEGIN
				INSERT INTO TRF_CLIENT VALUES(0,@ClientName,@ClientPassport);
				INSERT INTO TRF_ACCOUNT VALUES(@AccountId,0,@StartTariff,@StartBalance);
			END
			ELSE 
			BEGIN
				INSERT INTO TRF_CLIENT VALUES(@count+1,@ClientName,@ClientPassport);
				INSERT INTO TRF_ACCOUNT VALUES(@AccountId,@count+1,@StartTariff,@StartBalance);
			END
		END
END
	


